-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE WarehouseManagement
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		duylv00360
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE GetSingleOrder 
	-- Add the parameters for the stored procedure here
	@OrderId int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here	
    SELECT		m.MaterialCode MaMatHang,
				m.MaterialName TenMatHang,
				ml.Quantity	SoLuong,
				w.WarehouseID,
				w.WarehouseName KhoChua,
				ml.PricePerUnit GiaSanPham
		
	FROM		MaterialLoaf ml
	LEFT JOIN	Material m
	ON			m.MaterialID = ml.MaterialID
	LEFT JOIN	Warehouse w
	ON			ml.WarehouseID = w.WarehouseID
	WHERE		ml.OrderID = @OrderId
	UNION 
	SELECT		p.ProductCode MaMatHang,
				p.ProductName TenMatHang,
				pl.Quantity SoLuong,
				w.WarehouseID,
				w.WarehouseName KhoChua,
				pl.PricePerUnit GiaSanPham
			 
	FROM		ProductLoaf pl
	LEFT JOIN	Product p
	ON			pl.ProductID = p.ProductID
	LEFT JOIN	Warehouse w
	ON			pl.WarehouseID = w.WarehouseID
	WHERE		pl.OrderID = @OrderId
    
END
GO
/*
USE WarehouseManagement
GO
EXEC GetSingleOrder @orderId = 40
*/